#include "projectdb.h"
#include <QtSql/qsql.h>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QDebug>
#include <QDateTime>
#include <cmath>
bool ProjectDB::isOpen=false;
QSqlDatabase ProjectDB::db;
ProjectDB::ProjectDB()
{
   if (!isOpen)
       OpenDB();
}
bool ProjectDB::OpenDB()
{
    if(QSqlDatabase::contains("GBond"))
        db = QSqlDatabase::database("GBond");
    else
        db = QSqlDatabase::addDatabase("QSQLITE", "GBond");

  //    db = QSqlDatabase::addDatabase("QSQLITE");
//    db=new QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE")); //添加数据库驱动

    db.setDatabaseName("c://model-2000/main2.db");
    db.open();
    isOpen=true;
    return true;
}
bool ProjectDB::CloseDB()
{
    isOpen=false;
    /*
//    QString DBname=db.connectionName();
    db.close();
  //  delete db;
  //  db=NULL;

    QString dataName;
    {
        dataName = QSqlDatabase::database().connectionName();
    }//超出作用域，隐含对象QSqlDatabase::database()被删除。
    QSqlDatabase::removeDatabase(dataName);
//    QSqlDatabase::removeDatabase("GBond");//不推荐。

 //   QSqlDatabase::removeDatabase(DBname);
    */
    QString connection;
    connection = db.connectionName();
    db.close();
    db = QSqlDatabase();
    db.removeDatabase(connection);
    return true;
}

// 根据是否为标准端口决定9-2的采样速率
int ProjectDB::getSmpRate92(bool bIsStandard)
{
        int iReturn=0;
        QSqlQuery qXTCS("select * from muinfo",db);
     //   QSqlQuery qXTCS;
   //     qXTCS.exec("select * from S_XTCS");
        while(qXTCS.next())
        {
            if(bIsStandard)
                iReturn=qXTCS.value(2).toInt();            //SmpRate92_S
            else
                iReturn=qXTCS.value(2).toInt();            //SmpRate92_T
         }
         qXTCS.clear();         
         return iReturn;
}

void ProjectDB::getXTCS()
{
  //  QSqlQuery qXTCS;
    HGQ_Channel=0;
    TBFS=0;
    BZXZ=0;
    BCXZ=0;
    bzxdy="";
    bcxdy="";
    mCTPT=0;
    edyc_dl="0";
    edyc_dy="0";
    m_BCBC=0;
    m_JCBC=0;
    m_Tbsx=0;
    sysXs="";
    sysBc="";
    sysJc="";
    m_CTJD="0.2";
    m_PTJD="0.2";
    m_Bjcs=5;
    m_Cyys=5;
    m_Cyfs="0";
    m_Mac="";
    m_iSuanfa=0;
    QSqlQuery qXTCS("select * from S_XTCS",db);
  //  QSqlQuery qXTCS;
  //  qXTCS.exec("select * from S_XTCS");
    if(qXTCS.next())
    {
            HGQ_Channel=qXTCS.value(12).toInt()-1;
            sysXs=qXTCS.value(13).toString();
            sysBc=qXTCS.value(16).toString();
            sysJc=qXTCS.value(17).toString();
            TBFS=qXTCS.value(25).toInt();
            BZXZ=qXTCS.value(28).toInt();
            bzxdy=qXTCS.value(29).toString();
            BCXZ=qXTCS.value(30).toInt();
            bcxdy=qXTCS.value(31).toString();
            SmpRate92=qXTCS.value(21).toInt();
            repeat= qXTCS.value(23).toInt();
            bfbzero=qXTCS.value(24).toString();
            mCTPT=qXTCS.value(4).toInt();
            edyc_dl=qXTCS.value(32).toString();
            edyc_dy=qXTCS.value(33).toString();
            m_BCBC=qXTCS.value(2).toDouble();
            m_JCBC=qXTCS.value(1).toDouble();
            m_BCBC1=qXTCS.value(34).toDouble();
            m_JCBC1=qXTCS.value(35).toDouble();
            m_BCBC2=qXTCS.value(36).toDouble();
            m_JCBC2=qXTCS.value(37).toDouble();
            m_BCBC3=qXTCS.value(38).toDouble();
            m_JCBC3=qXTCS.value(39).toDouble();
            m_BCBC4=qXTCS.value(40).toDouble();
            m_JCBC4=qXTCS.value(41).toDouble();
            m_BCBC5=qXTCS.value(42).toDouble();
            m_JCBC5=qXTCS.value(43).toDouble();
            m_BCBC6=qXTCS.value(44).toDouble();
            m_JCBC6=qXTCS.value(45).toDouble();
            m_BCBC7=qXTCS.value(46).toDouble();
            m_JCBC7=qXTCS.value(47).toDouble();
            m_BCBC8=qXTCS.value(48).toDouble();
            m_JCBC8=qXTCS.value(49).toDouble();
            m_BCBC9=qXTCS.value(50).toDouble();
            m_JCBC9=qXTCS.value(51).toDouble();
            m_Tbsx=qXTCS.value(5).toInt();
            m_CTJD=qXTCS.value(7).toString();
            m_PTJD=qXTCS.value(6).toString();
            m_Bjcs=qXTCS.value(10).toInt();
            m_Cyys=qXTCS.value(11).toInt();
            m_Cyfs=qXTCS.value(8).toString();
            m_Mac=qXTCS.value(3).toString();
            m_iSuanfa=qXTCS.value(15).toInt();
     }
    qXTCS.clear();
}
void ProjectDB::setXTCS()
{
  //  QSqlQuery qXTCS;
    QString s=QString::number(HGQ_Channel);
    QString m=QString::number(TBFS);
    QString bzxz=QString::number(BZXZ);
    QString bcxz=QString::number(BCXZ);
    QString smprate92=QString::number(SmpRate92);
    QString irepeat=QString::number(repeat);
    QString ctpt=QString::number(mCTPT);
    QString bcbc=QString::number(m_BCBC);
    QString jcbc=QString::number(m_JCBC);
    QString bcbc1=QString::number(m_BCBC1);
    QString jcbc1=QString::number(m_JCBC1);
    QString bcbc2=QString::number(m_BCBC2);
    QString jcbc2=QString::number(m_JCBC2);
    QString bcbc3=QString::number(m_BCBC3);
    QString jcbc3=QString::number(m_JCBC3);
    QString bcbc4=QString::number(m_BCBC4);
    QString jcbc4=QString::number(m_JCBC4);
    QString bcbc5=QString::number(m_BCBC5);
    QString jcbc5=QString::number(m_JCBC5);
    QString bcbc6=QString::number(m_BCBC6);
    QString jcbc6=QString::number(m_JCBC6);
    QString bcbc7=QString::number(m_BCBC7);
    QString jcbc7=QString::number(m_JCBC7);
    QString bcbc8=QString::number(m_BCBC8);
    QString jcbc8=QString::number(m_JCBC8);
    QString bcbc9=QString::number(m_BCBC9);
    QString jcbc9=QString::number(m_JCBC9);
    QString tbsx=QString::number(m_Tbsx);
    QString bjcs=QString::number(m_Bjcs);
    QString cyys=QString::number(m_Cyys);
    QString suanfa=QString::number(m_iSuanfa);
    QString sql="update S_XTCS set channel_hgq="+s +",tbfs="+m +",bzxz=" + bzxz
                +",bcxz="+bcxz;
    sql+=",cslx="+sysXs;            //测量类型改为系统系数;
    sql+=",lubo_net_port="+sysBc;            //测量类型改为系统比差系数;
    sql+=",lubo_zhouqi_cnt="+sysJc;            //测量类型改为系统角差系数;
    if (bzxdy!="")
        sql+=",bzxdy="+bzxdy;
    if (bcxdy!="")
        sql+=",bcxdy="+bcxdy;
    sql+=",smprate92_T="+smprate92;
    sql+=",times_c="+irepeat;
    sql+=",zero_bfb="+bfbzero;
    sql+=",ctpt="+ctpt;
    if (ctpt=="0")
        sql+=",edyc_dl="+edyc_dl;
    else
        sql+=",edyc_dy="+edyc_dy;
    sql+=",XTBC="+bcbc;
    sql+=",XTJC="+jcbc;
    sql+=",XTBC1="+bcbc1;
    sql+=",XTJC1="+jcbc1;
    sql+=",XTBC2="+bcbc2;
    sql+=",XTJC2="+jcbc2;
    sql+=",XTBC3="+bcbc3;
    sql+=",XTJC3="+jcbc3;
    sql+=",XTBC4="+bcbc4;
    sql+=",XTJC4="+jcbc4;
    sql+=",XTBC5="+bcbc5;
    sql+=",XTJC5="+jcbc5;
    sql+=",XTBC6="+bcbc6;
    sql+=",XTJC6="+jcbc6;
    sql+=",XTBC7="+bcbc7;
    sql+=",XTJC7="+jcbc7;
    sql+=",XTBC8="+bcbc8;
    sql+=",XTJC8="+jcbc8;
    sql+=",XTBC9="+bcbc9;
    sql+=",XTJC9="+jcbc9;
    sql+=",is_com_jyy="+tbsx;
    sql+=",port_com_hgq="+bjcs;
    sql+=",port_net_hgq="+cyys;
    sql+=",port_net_jyy='"+ m_CTJD +"'";
    sql+=",port_com_jyy="+ m_PTJD;
    sql+=",channel_jyy='"+m_Cyfs+"'";
    sql+=",edec_jyy='"+m_Mac +"'";
    sql+=",bjcs='"+suanfa +"'";
    QSqlQuery qXTCS(sql,db);
  //  QSqlQuery qXTCS;
  //  qXTCS.exec(sql);
    qXTCS.clear();
}

QSqlQuery ProjectDB::openWcxx()
{
    QSqlQuery qWcxx("select * from wcxx",db);
    return qWcxx;
}
QSqlQuery ProjectDB::openWcxxforSql(QString sql)
{
    QSqlQuery qWcxx(sql,db);
    return qWcxx;
}
QSqlQuery ProjectDB::openWcdata(QString xh)
{
    QString sql="select * from wcdata where xh='"+xh +"'";
    QSqlQuery qWcdata(sql,db);
    return qWcdata;
}
QSqlQuery ProjectDB::openWcdataforSj(QString sj)
{
    QString sql="select * from wcdata where sj='"+sj +"'";
    QSqlQuery qWcdata(sql,db);
    return qWcdata;
}

void ProjectDB::updateWcxxDate(QString xh,QString date)
{
    QSqlQuery qWcxx("update wcxx set date='"+date +"' where xh='"+xh +"'",db);
    qWcxx.clear();
}

QString ProjectDB::insertWcxx(QString tbfs,QString bzxz,QString bcxz,QString edyc)
{
    QString sxh="";
    QSqlQuery qWcxx("select * from wcxx",db);
    if (qWcxx.last())
    {
        int xh=qWcxx.value(0).toInt();
        if(xh>=999)
        {
            if (qWcxx.first())
            {
                while(qWcxx.next())
                {
                    int i=qWcxx.value(0).toInt();
                    i--;
                    QString oxh=qWcxx.value(0).toString();
                    if (i<10)
                        sxh="00"+QString::number(i);
                    else if(i<100)
                        sxh="0"+QString::number(i);
                    else if(i<1000)
                        sxh=QString::number(i);
                    if (i==0)
                    {
                        QSqlQuery delWcxx("delete from wcxx where xh='"+oxh +"'",db);
                        delWcxx.clear();
                    }
                    else
                    {
                        QSqlQuery updateWcxx("update wcxx set xh='"+sxh +"' where xh='"+oxh +"'",db);
                        updateWcxx.clear();
                    }
                }
            }
            QSqlQuery qWcdata("select * from wcdata",db);
            QString oxh="";
            while(qWcdata.next())
            {
                int i=qWcdata.value(0).toInt();
                int result=QString::compare(oxh, qWcdata.value(0).toString(), Qt::CaseInsensitive);
                if (result!=1)
                {
                    oxh=qWcdata.value(0).toString();
                    i--;
                    if (i<10)
                        sxh="00"+QString::number(i);
                    else if(i<100)
                        sxh="0"+QString::number(i);
                    else if(i<1000)
                        sxh=QString::number(i);
                    if (i==0)
                    {
                        QSqlQuery delWcxx("delete from wcdata where xh='"+oxh +"'",db);
                        delWcxx.clear();
                    }
                    else
                    {
                        QSqlQuery updateWcxx("update wcdata set xh='"+sxh +"' where xh='"+oxh +"'",db);
                        updateWcxx.clear();
                    }
                }
            }
        }
        else
        {
            xh++;
            if (xh<10)
                sxh="00"+QString::number(xh);
            else if(xh<100)
                sxh="0"+QString::number(xh);
            else if(xh<1000)
                sxh=QString::number(xh);
        }
    }
    else
    {
        sxh="001";
    }
    qWcxx.clear();
    QString sql;
    sql="insert into wcxx values('";
    sql+=sxh;
    sql+="','";
    sql+=tbfs;
    sql+="','";
    sql+=bzxz;
    sql+="','";
    sql+=bcxz;
    sql+="','";
    sql+=edyc;
    sql+="','";
    sql+="')";
    QSqlQuery Wcxx(sql,db);
    Wcxx.clear();
    return sxh;
}

void ProjectDB::insertWcdata(QString xh,QString bzbfd,QString bcbfd,QString bzyxz,QString bcyxz,QString bc,QString jc)
{
    QString sql;
    sql="insert into wcdata values('";
    sql+=xh;
    sql+="','";
    sql+=bzbfd;
    sql+="','";
    sql+=bcbfd;
    sql+="','";
    sql+=bzyxz;
    sql+="','";
    sql+=bcyxz;
    sql+="','";
    sql+=bc;
    sql+="','";
    sql+=jc;
    sql+="')";
    QSqlQuery Wcdata(sql,db);
    Wcdata.clear();
}
void ProjectDB::updateWcdata(QString xh,QString bzbfd,QString bcbfd,QString bzyxz,QString bcyxz,QString bc,QString jc)
{
    QSqlQuery selectWcdata("select * from wcdata where xh='"+xh +"'",db);
    double xbfd=100;
    QString bfd="";
    while(selectWcdata.next())
    {
        double temp=std::abs(selectWcdata.value(1).toDouble()-bzbfd.toDouble());
        if (temp<xbfd)
        {
            xbfd=temp;
            bfd=selectWcdata.value(1).toString();
        }
    }

    QString sql;
    sql="update wcdata set bzbfd='";
    sql+=bzbfd;
    sql+="',bcbfd='";
    sql+=bcbfd;
    sql+="',bzyxz='";
    sql+=bzyxz;
    sql+="',bcyxz='";
    sql+=bcyxz;
    sql+="',bc='";
    sql+=bc;
    sql+="',jc='";
    sql+=jc;
    sql+="' where xh='";
    sql+=xh;
    sql+="'and bzbfd='";
    sql+=bfd;
    sql+="'";
    QSqlQuery Wcdata(sql,db);
    Wcdata.clear();
}
int ProjectDB::getWcxxCount()
{
    int tempIdx=0;
    QSqlQuery query("select COUNT(*) from wcxx",db);
    if (query.next()){
      tempIdx =  query.value(0).toInt();
    }
    query.clear();
    return tempIdx;
}
int ProjectDB::getWcxxCountForSql(QString sql)
{
    QString s="select COUNT(*) from wcxx ";
    s +=sql;
    int tempIdx=0;
    QSqlQuery query(s,db);
    if (query.next()){
      tempIdx =  query.value(0).toInt();
    }
    query.clear();
    return tempIdx;
}
void ProjectDB::delWc(QString xh)
{
    QSqlQuery qdelWcxx("delete from wcxx where xh='"+xh +"'",db);
    qdelWcxx.clear();
    QSqlQuery qWcxx("select * from wcxx",db);
    int ixh=xh.toInt();
    QString sxh="";
    while(qWcxx.next())
    {
        int i=qWcxx.value(0).toInt();
        if (i>ixh)
        {
            QString oxh=qWcxx.value(0).toString();
            i--;
            if (i<10)
                sxh="00"+QString::number(i);
            else if(i<100)
                sxh="0"+QString::number(i);
            else if(i<1000)
                sxh=QString::number(i);
            QSqlQuery updateWcxx("update wcxx set xh='"+sxh +"' where xh='"+oxh +"'",db);
            updateWcxx.clear();
        }
    }
    QSqlQuery qdelWcdata("delete from wcdata where xh='"+xh +"'",db);
    qdelWcdata.clear();
    QSqlQuery qWcdata("select * from wcdata",db);
    QString oxh="";
    while(qWcdata.next())
    {
        int i=qWcdata.value(0).toInt();
        if (i>ixh)
        {
            int result=QString::compare(oxh, qWcdata.value(0).toString(), Qt::CaseInsensitive);
            if (result!=1)
            {
                oxh=qWcdata.value(0).toString();
                i--;
                if (i<10)
                    sxh="00"+QString::number(i);
                else if(i<100)
                    sxh="0"+QString::number(i);
                else if(i<1000)
                    sxh=QString::number(i);
                QSqlQuery updateWcdata("update wcdata set xh='"+sxh +"' where xh='"+oxh +"'",db);
                updateWcdata.clear();
            }
        }
    }
}
void ProjectDB::DelAll()
{
    QSqlQuery qdelWcxx("delete from wcxx",db);
    qdelWcxx.clear();
    QSqlQuery qdelWcdata("delete from wcdata",db);
    qdelWcdata.clear();
}

int ProjectDB::newTest()
{
    QSqlQuery allRecords("select * from wcxx;",db);

    int SerialNum[110] = {0x00};

    char i = 0;

    while(allRecords.next())
    {
        SerialNum[allRecords.value(0).toInt() - 1] = 1;
        i++;
    }

    qDebug()<<"Find"<<QString::number( i)<<"records in db";

    if (i == 100)
    {
        //delete xh="001"
        QSqlQuery delTheFirst("delete from wcxx where xh='001'",db);
        delTheFirst.clear();

        QSqlQuery delTheFirst_data("delete from wcdata where xh='001'",db);
        delTheFirst_data.clear();

        SerialNum[0] = 0;
    }

    for(i = 0; i < 100; i++)
    {
        if(SerialNum[i] == 0) return i+1;
    }

    return -1;
}

void ProjectDB::addNewRecord(int xh, QString tbfs, QString primaryValue,
                             QString stdInput, QString dutSelection)
{
    QString index;

    if(xh < 10)
    {
        index = "00" + QString::number(xh);
    }
    else if(xh < 100)
    {
        index = "0" + QString::number(xh);
    }
    else
    {
        index = QString::number(xh);
    }

    QDateTime qdatetime=QDateTime::currentDateTime();
    QString date=qdatetime.toString("yyyy-MM-dd")+"-"+qdatetime.toString("hh-mm-ss");

    //insert into wcxx values('002','同步输出','200KV, 400A','100/√3V, 1A','小电压,1V','2015-08-03'
    QString requestStr = "insert into wcxx values(";
    requestStr += "'" + index + "',";
    requestStr += "'" + tbfs + "',";
    requestStr += "'" + stdInput + "',";
    requestStr += "'" + dutSelection + "',";
    requestStr += "'" + primaryValue + "',";
    requestStr += "'" + date + "')";

    qDebug()<<requestStr;

    QSqlQuery newRecord(requestStr,db);
    newRecord.clear();
}

void ProjectDB::addData2Record(int xh, QString dutName,QString RMS, QString dutRMS, QString ratioError, QString phaseError)
{
    QString index;

    if(xh == 0) return;

    if(xh < 10)
    {
        index = "00" + QString::number(xh);
    }
    else if(xh < 100)
    {
        index = "0" + QString::number(xh);
    }
    else
    {
        index = QString::number(xh);
    }

    //wcdata(xh varchar(10),bzbfd varchar(10),bcbfd varchar
    //(10),bzyxz varchar(15),bcyxz varchar(15),bc varchar(15),jc varchar(15));

    QString requestStr = "insert into wcdata values(";
    requestStr += "'" + index + "',";
    requestStr += "'" + dutName + "',";
    requestStr += "'" + RMS + "',";
    requestStr += "'" + RMS + "',";
    requestStr += "'" + dutRMS + "',";
    requestStr += "'" + ratioError + "',";
    requestStr += "'" + phaseError + "')";

    qDebug()<<requestStr;

    QSqlQuery newRecord(requestStr,db);
    newRecord.clear();
}
